RDBMS SOLUTION
ASSIGNMENT 7
SET A Q. 1 SOLUTION
Write a package, which consists of one procedure and one function. Pass college code as a parameter to procedure and display details of college. Write a function which will return teacher name having maximum salary.
Source code:
/* assignment 7 set a q 1 solution
This is the last exercise of the Rdbms part
First we will create tables and then insert records and then we wil create packages*/
Create table college
(
code number not null primary key,
cname varchar2(40),
address varchar2(40)
)
create table teacher
(
tid number not null primary key,
tname varchar2(40),
tqualification varchar2(40),
spelization varchar2(40),
salary number
)
create table c_t
(
code number references college(code),
tid number references teacher(tid)
)
insert into college values(101,'C1','A1')
insert into college values(102,'C2','A2')
insert into teacher values(1,'T1','Q1','S1',10000)
insert into teacher values(2,'T2','Q2','S2',30000)
insert into teacher values(3,'T3','Q3','S3',40000)
insert into teacher values(4,'T4','Q4','S4',60000)
insert into teacher values(5,'T5','Q5','S5',70000)
insert into c_t values(101,1)
insert into c_t values(101,2)
insert into c_t values(101,3)
insert into c_t values(101,4)
insert into c_t values(101,5)
insert into c_t values(102,2)
insert into c_t values(102,3)
insert into c_t values(102,4)
insert into c_t values(102,5)
/*package*/
/*Package definition*/
CREATE OR REPLACE PACKAGE pkg1 IS
FUNCTION pkg_fuc1 RETURN VARCHAR;
PROCEDURE pkg_p1(p_ccode IN NUMBER, p_clgdet OUT college%ROWTYPE);
END pkg1;
/* package body*/
create or replace package body pkg1
as
function pkg_fuc1
return varchar is
p_tname varchar(40);
begin
select t.tname into p_tname from teacher t where t.salary=(select min(salary) from teacher);
return p_tname;
end;
procedure pkg_p1 (p_ccode in number,p_clgdet out college%rowtype)
is
begin
select * into p_clgdet from college cc where cc.code=p_ccode;
end;
end pkg1;
/*Executing package*/
Declare
c_rec college%rowtype;
code college.code%type:=:Code;
tname varchar2(40);
Begin
tname:=pkg1.pkg_fuc1();
pkg1.pkg_p1(code,c_rec);
Dbms_output.put_line('Teacher with lowest salary is '||tname);
dbms_output.put_line('Collage name: '||c_rec.cname);
dbms_output.put_line('Collage address: '||c_rec.address);
end;
/* Explaination:
This is a SQL code block that defines a package |pkg1| containing two subprograms: a function |pkg_fuc1| and a procedure |pkg_p1|.
The |pkg_fuc1| function selects the name of the teacher with the minimum salary from the |teacher| table and returns it as a |varchar|. The |pkg_p1| procedure takes an input parameter |p_ccode| of type |NUMBER| and an output parameter |p_clgdet| of type |college%ROWTYPE|, and selects the corresponding row from the |college| table using the input parameter.
The package body is then created to implement the functionality of the subprograms. The |pkg_fuc1| function implementation selects the teacher name with the minimum salary and returns it. The |pkg_p1| procedure implementation selects the college row with the input code and returns it through the |p_clgdet| output parameter.
Finally, the package is executed in a PL/SQL block. The |pkg_fuc1| function is called to retrieve the name of the teacher with the minimum salary and stored in the |tname| variable. The |pkg_p1| procedure is called with the input parameter |code| and the output parameter |c_rec| to retrieve the college details for the input code. The college details are then printed to the console using the |dbms_output.put_line| function.
*/
0 Comments